package com.ljh.dao.impl;

import com.ljh.bean.Role;
import com.ljh.bean.Users;
import com.ljh.dao.DBUtils;
import com.ljh.dao.UsersDao;
import com.ljh.util.StudentEnum;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UsersDaoImpl extends DBUtils implements UsersDao {
    @Override
    public Users login(String username, String password) {
        Users users= null;
        try {
            String sql="select * from users where loginname=? and password=?";
            ArrayList arrayList = new ArrayList();
            arrayList.add(username);
            arrayList.add(password);
            resultSet = query(sql, arrayList);
            if(resultSet==null){
                return null;
            }
            while(resultSet.next()){
                users=new Users();
                users.setLoginName(username);
                users.setRealName(resultSet.getString("realname"));
                users.setUserId(resultSet.getInt("userid"));
                users.setRoleId(resultSet.getInt("roleid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll();
        }
        return users;
    }

    @Override
    public List<Users> getUsersList(int pageIndex, int pageSize) {
        List<Users> usersList=new ArrayList<Users>();
        try {
            String sql="select userid,loginname,realname,rolename from users u,role r where u.roleid=r.roleid  limit ?,?";
            List params=new ArrayList();
            params.add((pageIndex-1)*pageSize);
            params.add(pageSize);

            resultSet = query(sql, params);
            while(resultSet.next()){
                //1.取出各表的数据
                Users users = new Users();
                users.setUserId(resultSet.getInt("userid"));
                users.setLoginName(resultSet.getString("loginname"));
                users.setRealName(resultSet.getString("realname"));

                Role role = new Role();
                role.setRoleName(resultSet.getString("rolename"));
                //2.建立关系
                users.setRole(role);
                usersList.add(users);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll();
        }
        return usersList;
    }

    @Override
    public int total() {
        int total=0;
        try {
            String sql="select count(1) from users u,role r where u.roleid=r.roleid ";
            resultSet = query(sql, null);
            while(resultSet.next()){
                total=resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll();
        }
        return total;
    }

    @Override
    public Users findById(int sid) {
        Users user = new Users();
        try {
            String sql="select * from users where userid=?";
            List params=new ArrayList();
            params.add(sid);

            resultSet=query(sql,params);
            while(resultSet.next()){
                user.setUserId(resultSet.getInt("userid"));
                user.setRealName(resultSet.getString("realname"));
                user.setLoginName(resultSet.getString("loginname"));
                user.setRoleId(resultSet.getInt("roleid"));
                user.setAddress(resultSet.getString("address"));
                user.setCardId(resultSet.getString("cardid"));
                user.setDesc(resultSet.getString("desc"));
                user.setEmail(resultSet.getString("email"));
                user.setPhone(resultSet.getString("phone"));
                user.setSex(resultSet.getInt("sex"));
                user.setPassWord(resultSet.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return user;
    }

    @Override
    public int updateUser(Users user) {
        int update = 0;
        try {
            String sql="update users set loginname=?,realname=?,roleid=?,phone=?,address=?,email=?,sex=? where userid=?";
            List params=new ArrayList();
            params.add(user.getLoginName());
            params.add(user.getRealName());
            params.add(user.getRoleId());
            params.add(user.getPhone());
            params.add(user.getAddress());
            params.add(user.getEmail());
            params.add(user.getSex());
            params.add(user.getUserId());

            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int delUser(String sid) {
        int update = 0;
        try {
            String sql="delete from users where userid=?";
            List params=new ArrayList();
            params.add(sid);
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int insertUser(Users user) {
        int update = 0;
        try {
            String sql = "insert into users(loginname,password,realname,sex,email,address,phone,cardid,roleid) values(?,?,?,?,?,?,?,?,?)";
            List params = new ArrayList();
            params.add(user.getLoginName());
            params.add(user.getPassWord());
            params.add(user.getRealName());
            params.add(user.getSex());
            params.add(user.getEmail());
            params.add(user.getAddress());
            params.add(user.getPhone());
            params.add(user.getCardId());
            params.add(user.getRoleId());
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int delUsers(String loginNames) {
        int update = 0;
        try {
            List params=new ArrayList();
            List sim = new ArrayList();
            String[] array = loginNames.split(",");
            for (int i = 0; i < array.length; i++){
                params.add(array[i]);
                if (array.length > 1) {
                    sim.add("?");
                }
            }
            String sql= "";
            if (array.length > 1) {
                sql="delete from users where loginname in("+ sim.toString().replace("[", "").replace("]", "")+ ")";
            } else {
                sql="delete from users where loginname in(?)";
            }
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }
}